#!/usr/bin/env python
# -*- coding: utf-8 -*-

"""
Python API for manipulating notify on renren
"""

__author__ = "LongFei Meng"
__copyright__ = "Copyright 2011-2012 Renren-inc"
__credits__ = ["Longfei Meng"]
__license__ = "MIT <http://www.opensource.org/licenses/mit-license.php>"
__version__ = "0.1"
__maintainer__ = "LongFei Meng"
__email__ = "alphaperfect[AT]gmail[DOT]com"
__status__ = "Development"""


import os
import re
import sys
import time
import getopt
import atexit
import socket
import inspect
import readline
import datetime

from xml.dom import minidom, Node

#import xml.etree.ElementTree as ET
#from xml.etree.ElementTree import ElementTree

#sys.path.append("/data/home/longfei.meng/soft/MySQL-python-1.2.3")
#sys.path.append("/data/home/longfei.meng/soft/MySQL-python-1.2.3/build/lib.linux-x86_64-2.4")
sys.path.append("/data/home/longfei.meng/soft/install/mysqlpy")
import MySQLdb

def completer(word, index):
  matches = [c for c in candidates if c.startswith(word)]
  try:
    return matches[index] + " "
  except IndexError:
    pass

status = 0
ic = None
historyPath = "./."+sys.argv[0]+".history"
def save_history(historyPath=historyPath):
    readline.write_history_file(historyPath)

if os.path.exists(historyPath):
    readline.read_history_file(historyPath)

atexit.register(save_history)

readline.set_completer(completer)
readline.parse_and_bind("tab: complete")

ISO_TIME_FORMAT="%Y-%m-%d %H:%M:%S"

def query_by_time(host, user, passwd, db, port):
  db = MySQLdb.connect(host, user, passwd, db, port)
  c=db.cursor()
  c.execute("select count(*) from index_62 where to_id = 433553862")
  print "select result:", c.fetchall()
  c.close()
  db.close()


class QueryRunner:
  def __init__(self, host, user, passwd, db, port, out_file_name):
    self.db_conn_ = MySQLdb.connect(host, user, passwd, db, port)
    self.db_conn_.autocommit(True)
    self.cursor_ = self.db_conn_.cursor()
    self.out_file_name_ = out_file_name
   
  def release(self):
    self.cursor_.close()
    self.db_conn_.close()
    print "Close cursor and database"

  def query_minid(self, table_name):
    query_str = "select min(id) as minid from %s " % (table_name)
    self.cursor_.execute(query_str)
    return self.cursor_.fetchall()
  def get_min_time(self, table_name):
    print "----process %s begin------" % (table_name)
    #query_str = "select min(time) from %s " % (table_name)
    query_str = "select min(time) from %s where bigtype = 3" % (table_name)
    start_time = time.time()
    self.cursor_.execute(query_str)
    min_time =  self.cursor_.fetchall()
    out_file = open(self.out_file_name_, 'a')
    out_str  = "get min time from %s cost %s" % (table_name, (time.time() - start_time))
    print out_str
    out_file.write(out_str + "\n")
    out_file.close()
    return min_time[0][0]
  def exec_sql(self, sql):
    self.cursor_.execute(sql)
    return self.cursor_.fetchall()
  def get_index_table_name(self):
    sql = "show tables"
    all_tables = self.exec_sql(sql)
    index_tables = []
    patter = re.compile(r"^index_[0-9]+")
    for table_name in all_tables:
      if (patter.match(table_name[0])):
        index_tables.append(table_name[0])
    return index_tables

  def garbage_process(self, max_time_str, del_flag):
    index_tables = self.get_index_table_name()
    index_tables.sort()
    start_time = time.time()
    for table_name in index_tables:
      min_time = self.get_min_time(table_name)
      sql = self.process_interval(table_name, min_time, 3600, max_time_str, del_flag)
      if (sql == ""):
        continue
    out_file = open(self.out_file_name_, 'a')
    out_str  = "total cost %s" % (time.time() - start_time)
    print out_str
    out_file.write(out_str + "\n")
    out_file.close()
  
  def process_interval(self, table_name, begin_time, interval, max_time_str, del_flag):   
    begin_time_str = time.strftime(ISO_TIME_FORMAT, time.localtime(time.time()))
    tm = time.strptime(max_time_str, ISO_TIME_FORMAT)
    max_time = datetime.datetime(*tm[:6])
    out_file = open(self.out_file_name_, 'a')
    total_count = 0;
    for i in range(0, 30000):
      begin = begin_time + datetime.timedelta(seconds = (i * interval))
      begin_str = begin.strftime(ISO_TIME_FORMAT)
      an_hour = begin_time + datetime.timedelta(seconds = ((i + 1) * interval))
      an_hour_str = an_hour.strftime(ISO_TIME_FORMAT)
      if (an_hour > max_time):
        out_str = "beyond limit an_hour %s max_time %s" % (an_hour_str, max_time_str)
        print out_str
        out_file.write(out_str + "\n")
        break
      sql = ""
      out_count = -1;
      out_count_str = ""
      start_time = time.time()
      try:
        if (del_flag):
          sql = "delete from %s where time between '%s' and '%s' and bigtype = 3" % (table_name, begin_str, an_hour_str)
          out_count = self.cursor_.execute(sql)
          #self.db_conn_.commit()
          total_count += out_count
        else:
          sql = "select count(*) from %s where time between '%s' and '%s' and bigtype = 3" % (table_name, begin_str, an_hour_str)
          out_count = self.exec_sql(sql)[0][0]
          total_count += out_count
        out_count_str  = "count %s sql %s - cost %s" % (out_count, sql, time.time() - start_time)
      except MySQLdb.Error, e:
        error_str =  "Error %d: %s" % (e.args[0], e.args[1])
        print error_str
        out_file.write(error_str + "\n")
      print out_count_str 
      out_file.write(out_count_str + "\n")
    end_time_str =  time.strftime(ISO_TIME_FORMAT, time.localtime(time.time()))
    total_count_str  = "total count %d begin %s end %s" % (total_count, begin_time_str, end_time_str)
    print total_count_str
    out_file.write(total_count_str + "\n")
    out_file.close()

class DbParser:
  def __init__(self, config_file_name):
    self.config_file_ = config_file_name 
    self.db_info_dict_ = self.get_all_notify_db(config_file_name)
    self.instance_name_ = ["notify_0", "notify_1", "notify_2", "notify_3", 
          "notify_4"]
  def parse_db_config(self, instance_name, config_xml):
   doc = minidom.parse(config_xml)
   instance_list = doc.getElementsByTagName("instance")
   for instance in instance_list:
     name = instance.attributes["name"]
     if (name.value == instance_name):
        return self.parse_db_instance(instance)
    
  def get_all_notify_db(self, config_xml):
    notify_dict = {}
    notify_dict["notify_0"] = self.parse_db_config("notify_0", config_xml)
    notify_dict["notify_1"] = self.parse_db_config("notify_1", config_xml)
    notify_dict["notify_2"] = self.parse_db_config("notify_2", config_xml)
    notify_dict["notify_3"] = self.parse_db_config("notify_3", config_xml)
    notify_dict["notify_4"] = self.parse_db_config("notify_4", config_xml)
    return notify_dict

  def get_index_dbinfo_by_uid(self, uid):
    pattern_dict = {} 
    pattern_dict["notify_0"] = r"\d+[01][0-9]$"
    pattern_dict["notify_1"] = r"\d+[23][0-9]$"
    pattern_dict["notify_2"] = r"\d+[45][0-9]$"
    pattern_dict["notify_3"] = r"\d+[67][0-9]$"
    pattern_dict["notify_4"] = r"\d+[89][0-9]$"
    keys = pattern_dict.keys();
    keys.sort()
    for key in keys:
      pattern = re.compile(pattern_dict[key])
      if pattern.match(uid):
        print "uid[%s] instance[%s] value[%s]" % (uid, key, pattern_dict[key])
        return self.db_info_dict_[key]

  def get_index_dbinfo_by_instance(self, instance_name):
    return self.db_info_dict_[instance_name]

  def parse_db_instance(self, instance):
    db_info_dict = {}
    server = instance.childNodes[1]
    attr_keys = server.attributes.keys()
    for key in attr_keys:
      db_info_dict[key] = server.attributes[key].value
      #print "key[%s] value[%s]" % (key, server.attributes[key].value)
    return db_info_dict
    #database_attr = server.attributes["database"]



def show_db_info(db_info_dict):
  keys = db_info_dict.keys()
  for key in keys:
    print "%s     %s\n" % (key, db_info_dict[key])

def update_database_desc():
  host = "root@t0.xntalk.d.xiaonei.com"
  cmd = "cd /data/xce_svn/XiaoNei/etc/ && svn up "
  ssh_cmd = "ssh -T %s '%s'" % (host, cmd)
  print "update database config:%s" % ssh_cmd
  os.system(ssh_cmd)

def get_database_config():
  cmd = "scp root@t0.xntalk.d.xiaonei.com:/data/xce_svn/XiaoNei/etc/database_desc.xml ."
  os.system(cmd)

def main():
  try:
    opt_list, arg_list = getopt.getopt(sys.argv[1:], "s:ad")
  except getopt.GetoptError:
    print(str(err))
    usage()
    sys.exit('Invalid argument:%s' % (sys.argv, ))
  instance_name = 'notify_0'
  all_flag = False
  del_flag = False
  instance_name_list = []
  for opt, arg in opt_list:
    if opt == "-s":
      instance_name_list.append(str(arg))
    elif opt == "-d":
      del_flag = True 
    elif opt == "-a":
      instance_name_list = ["notify_0", "notify_1", "notify_2", "notify_3", 
          "notify_4"]
    else:
      sys.exit('unhandled option')
  update_database_desc()
  get_database_config()
  db_info = DbParser("database_desc.xml")
  today_str = time.strftime('%Y-%m-%d', time.localtime(time.time()- 3600*24))
  for instance_name in instance_name_list:
    if (del_flag):
      out_file_name = instance_name + r"_db_delete_" + today_str + r".log" 
    else:
      out_file_name = instance_name + r"_db_query_" + today_str + r".log" 
    info_dict = db_info.get_index_dbinfo_by_instance(instance_name)
    qrunner = QueryRunner(info_dict["host"], 
        info_dict["user"], 
        info_dict["password"], 
        info_dict["database"], 
        (int)(info_dict["port"]),
        out_file_name)
    #qrunner.garbage_process("2011-11-11 00:00:00", del_flag)
    qrunner.garbage_process("2012-05-01 00:00:00", del_flag)
    qrunner.release()


if __name__ == "__main__":
  main()
  #uid = "433553862"
  #print "db:", get_index_dbinfo_by_uid(uid, "database_desc.xml")

